package com.bw.dao;

import com.bw.bean.SugarDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
//    （3） 按照第二大类交易额排名 sql
    @Select("select c2.name,sum(o.benefit_reduce_amount) `value` from order_info O\n" +
            "LEFT JOIN order_detail d on o.id=d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_category3 c3 on s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 on c3.category2_id=c2.id\n" +
            "GROUP BY c2.name ORDER BY `value` desc")
    List<SugarDTO> getList1();


    @Select("select t.name,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id \n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_trademark t ON s.tm_id=t.id \n" +
            "GROUP BY t.name")
    List<SugarDTO> getList2();

    @Select("select p.name,sum(o.final_total_amount) `value` from sku_info s \n" +
            "LEFT JOIN order_detail d ON s.id=d.sku_id \n" +
            "LEFT JOIN order_info o ON d.order_id=o.id\n" +
            "LEFT JOIN base_province p ON o.province_id=p.id\n" +
            "GROUP BY p.name")
    List<SugarDTO> getList3();

    @Select("call sp1()")
    List<SugarDTO> getList4();

    @Select("select p.spu_name name, sum(o.final_total_amount) `value`  from order_info o\n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN spu_info p ON s.spu_id=p.id\n" +
            "GROUP BY p.spu_name")
    List<SugarDTO> getList5();
}
